package com.wxtf.platform.main.proprietaryInquires;

import javax.servlet.http.HttpServletRequest;

import net.sf.json.JSONArray;
import net.sf.json.JSONObject;

import org.apache.commons.lang.StringUtils;
import org.apache.commons.logging.Log;
import org.apache.commons.logging.LogFactory;

import com.wxtf.platform.interfaces.CommonsCallProvider;
import com.wxtf.platform.plugins.user.UserBean;
import com.wxtf.platform.supers.LogicBase;
import com.wxtf.platform.tools.ConstantInfoHelper;
import com.wxtf.platform.tools.AccessDataBaseUtil;
/**
 * <li>Title: Employee.java</li>
 * <li>Project: baseplatform</li>
 * <li>Package: com.wxtf.platform.main.proprietaryInquires</li>
 * <li>Description: 独生子女员工</li>
 * <li>Copyright: Copyright (c) 2012</li>
 * <li>Company: wxthtf Technologies </li>
 * <li>Created on Sep 21, 2012 10:29:02 AM</li>
 * 
 * @author wangzhi
 * @version 1.0.0.0
 * 
 */
public class Employee extends LogicBase implements CommonsCallProvider{
	private static final Log log = LogFactory.getLog(Employee.class);

	public Employee(String jsonArray){

	}
	
	@Override
	public Object run() {
		Object msg = "";
		try {
			if("queryPage".equals(this.getMethodName())) {
				msg = this.select(this.getLogicData());
				return msg;
			} else if("query1".equals(this.getMethodName())) {
				msg = this.query1(this.getLogicData());
				return msg;
			}else if("done".equals(this.getMethodName())) {
				msg = this.done(this.getLogicData());
				return msg;
			}else if("not_done".equals(this.getMethodName())) {
				msg = this.not_done(this.getLogicData());
				return msg;
			}else if("banzhenglv".equals(this.getMethodName())) {
				msg = this.banzhenglv(this.getLogicData());
				return msg;
			} else {
				msg = "\"没有【" + this.getMethodName() + "】该方法\"";
			}
			if(msg == null || "[null]".equals(msg)) {
				msg = "\"\"";
			}
			if(msg.toString().startsWith("{") || msg.toString().startsWith("[")){
				return msg;
			}
			msg = ConstantInfoHelper.generateJSONString(msg.toString());
		} catch(Exception e) {
			msg = ConstantInfoHelper.generateErrorJSONString();
			log.error(e, e);
		} finally {
			this.clear();
		}
		return msg.toString();
	}

	String[] fields = {
			"tbe_birthday","reserve1","tbe_id","org_id","tbe_name","tbe_idcard","tbe_sex","tbe_age","tbe_nation","tbe_position","tbe_seniority","tbfi_spouse_name","tbfi_spouse_sex","tbfi_spouse_department","tbe_marital_status","tbe_children_status","chizheng"
	};
	@Override
	public Object select(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		//String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_employment_forms <> '31' and ((t2.tbfi_children_num='1' and t2.tbfi_valid='1') or (t2.tbfi_valid='0' and t2.tbe_spouse_id in (select tbe_id from tb_employee where tbe_id in (select tbe_id from tb_family_base where tbfi_children_num='1') ) ) ) ";
		String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t2.tbfi_children_num='1' ";
		if("init".equals(type)){
			sql = sql+" and t1.org_id in (" + user.getCurSubOrgId() + ") ";	
		} else if("tj_ser".equals(type)) {
			String ser_from= jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String orgIds = jsonObject.getString("ser_org");
			if(StringUtils.isBlank(orgIds)) {
				sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ")";
			} else {
				sql = sql + " and t1.org_id in (" + user.getCurSubOrgIds(orgIds) + ")";
			}
			if(StringUtils.isNotBlank(ser_from)) {
				sql = sql + " and t1.tbe_birthday >= '" + ser_from + "' ";
			}
			if(StringUtils.isNotBlank(ser_to)) {
				sql = sql + " and t1.tbe_birthday <= '" + ser_to + "' ";
			}	
		}
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname!=null&&sortname!=null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		//String querySql = "select t1.tbe_birthday,t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_age,t1.tbe_nation,t1.tbe_position,t1.tbe_seniority,t2.tbfi_spouse_name,t2.tbfi_spouse_sex,t2.tbfi_spouse_department,t1.tbe_marital_status,t1.tbe_children_status,(select count(*) from tbe_only_child_record tt1 where( (tt1.tbfi_id=t2.tbfi_id and t2.tbfi_valid='1') or (tt1.tbfi_id=(select tbfi_id from tb_family_base where tbe_id=t2.tbe_spouse_id) and t2.tbfi_valid='0' ))) as chizheng " + sql;
		String querySql = "select t1.tbe_birthday,t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_age,t1.tbe_nation,t1.tbe_position,t1.tbe_seniority,t2.tbfi_spouse_name,t2.tbfi_spouse_sex,t2.tbfi_spouse_department,t1.tbe_marital_status,t1.tbe_children_status,(select count(*) from tbe_only_child_record tt1 where tt1.tbfi_id =t2.tbfi_id and tt1.reserve2<>'1') as chizheng " + sql;
		
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);	
	}
     
	//已办证职工名单
	public Object done(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		//String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id left join tbe_only_child_record t3 on t3.tbfi_id=t2.tbfi_id where t2.tbfi_children_num='1' and t2.tbfi_id in (select tbfi_id from tbe_only_child_record ) ";		
		//String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_employment_forms <> '31' and ((t2.tbfi_children_num='1' and t2.tbfi_valid='1' and t2.tbfi_id in (select tbfi_id from tbe_only_child_record)) or (t2.tbfi_valid='0' and  t2.tbe_spouse_id in (select tbe_id from tb_family_base where tbfi_id in (select tbfi_id from tbe_only_child_record) and tbe_id in (select tbe_id from tb_family_base where tbfi_children_num='1') ) ) ) ";
		String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t2.tbfi_children_num='1' and t2.tbfi_id in (select o.tbfi_id from tbe_only_child_record o where o.reserve2<>'1') ";
		if("init".equals(type)) {
			sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		} else if("tj_ser".equals(type)) {
			String ser_from = jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String orgIds = jsonObject.getString("ser_org");
			if(StringUtils.isBlank(orgIds)) {
				sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ")";
			} else {
				sql = sql + " and t1.org_id in (" + user.getCurSubOrgIds(orgIds) + ")";
			}
			if(StringUtils.isNotBlank(ser_from)) {
				sql = sql + " and t1.tbe_birthday >= '" + ser_from + "' ";
			}
			if(StringUtils.isNotBlank(ser_to)) {
				sql = sql + " and t1.tbe_birthday <= '" + ser_to + "' ";
			}	
		}
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.tbe_birthday,t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_age,t1.tbe_nation,t1.tbe_position,t1.tbe_seniority,t2.tbfi_spouse_name,t2.tbfi_spouse_sex,t2.tbfi_spouse_department,t1.tbe_marital_status,t1.tbe_children_status,(select count(*) from tbe_only_child_record tt1 where tt1.tbfi_id =t2.tbfi_id and tt1.reserve2<>'1' ) as chizheng " + sql;	
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);	
	}

	
	//未办证职工名单
	public Object not_done(Object jsonArray) throws Exception {
		UserBean user = (UserBean)this.getRequest().getSession().getAttribute("userBean");
		JSONArray array = JSONArray.fromObject(jsonArray);
		JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
		String type= jsonObject.getString("type");
		//String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_employment_forms <> '31' and ((t2.tbfi_children_num='1' and t2.tbfi_valid='1' and t2.tbfi_id not in (select tbfi_id from tbe_only_child_record)) or (t2.tbfi_valid='0' and  t2.tbe_spouse_id in (select tbe_id from tb_family_base where tbfi_id not in (select tbfi_id from tbe_only_child_record) and tbe_id in (select tbe_id from tb_family_base where tbfi_children_num='1') ) ) ) ";
		String sql=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t2.tbfi_children_num='1' and t2.tbfi_id not in (select o.tbfi_id from tbe_only_child_record o where o.reserve2<>'1') ";
		if("init".equals(type)) {
			sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		} else if("tj_ser".equals(type)) {
			String ser_from = jsonObject.getString("ser_from");
			String ser_to = jsonObject.getString("ser_to");
			String orgIds = jsonObject.getString("ser_org");
			if(StringUtils.isBlank(orgIds)) {
				sql = sql + " and t1.org_id in (" + user.getCurSubOrgId() + ")";
			} else {
				sql = sql + " and t1.org_id in (" + user.getCurSubOrgIds(orgIds) + ")";
			}
			if(StringUtils.isNotBlank(ser_from)) {
				sql = sql + " and t1.tbe_birthday >= '" + ser_from + "' ";
			}
			if(StringUtils.isNotBlank(ser_to)) {
				sql = sql + " and t1.tbe_birthday <= '" + ser_to + "' ";
			}	
		}
		String sortname = this.getRequest().getParameter("sortname");
		String sortorder = this.getRequest().getParameter("sortorder");
		if(sortname != null && sortname != null) {
			sql += " order by " + sortname + " " + sortorder;
		}
		String countSql = "select count(*) " + sql;
		String querySql = "select t1.tbe_birthday,t1.reserve1,t1.tbe_id,t1.org_id,t1.tbe_name,t1.tbe_idcard,t1.tbe_sex,t1.tbe_age,t1.tbe_nation,t1.tbe_position,t1.tbe_seniority,t2.tbfi_spouse_name,t2.tbfi_spouse_sex,t2.tbfi_spouse_department,t1.tbe_marital_status,t1.tbe_children_status,(select count(*) from tbe_only_child_record tt1 where tt1.tbfi_id =t2.tbfi_id and tt1.reserve2<>'1' ) as chizheng" + sql;
		return AccessDataBaseUtil.query4SQLPagging(fields, countSql, querySql, this.getPage(), this.getPagesize(), null);	
	}
	
	//独生子女办证率
	public Object banzhenglv(Object jsonArray) throws Exception {
		UserBean user = (UserBean) this.getRequest().getSession().getAttribute("userBean");
		int done_num=0; //已辦證
		String banzhenglv="0"; //辦證率
		int num_all=0; // 應辦證
		String sql_all = " from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t2.tbfi_children_num='1' ";
		//String sql_all=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_employment_forms <> '31' and ((t2.tbfi_children_num='1' and t2.tbfi_valid='1') or (t2.tbfi_valid='0' and t2.tbe_spouse_id in (select tbe_id from tb_employee where tbe_id in (select tbe_id from tb_family_base where tbfi_children_num='1') ) ) ) ";
		String sql_done = " from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_status not in ('102-3','102-4','102-5') and t1.tbe_employment_forms <> '31' and t2.tbfi_children_num='1' and t2.tbfi_id in (select o.tbfi_id from tbe_only_child_record o where o.reserve2<>'1') ";
		//String sql_done=" from tb_employee t1 left join tb_family_base t2 on t1.tbe_id=t2.tbe_id where t1.tbe_employment_forms <> '31' and ((t2.tbfi_children_num='1' and t2.tbfi_valid='1' and t2.tbfi_id in (select tbfi_id from tbe_only_child_record)) or (t2.tbfi_valid='0' and  t2.tbe_spouse_id in (select tbe_id from tb_family_base where tbfi_id in (select tbfi_id from tbe_only_child_record) and tbe_id in (select tbe_id from tb_family_base where tbfi_children_num='1') ) ) ) ";
		sql_all = sql_all + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		sql_done = sql_done + " and t1.org_id in (" + user.getCurSubOrgId() + ") ";
		sql_all="select count(*) "+sql_all;
		sql_done = "select count(*) " + sql_done;
		num_all = AccessDataBaseUtil.queryForInt(sql_all, null);
		done_num = AccessDataBaseUtil.queryForInt(sql_done, null);
		java.text.DecimalFormat df = new java.text.DecimalFormat("#.##");
		if(num_all != 0) {
			banzhenglv= df.format((float) done_num / num_all * 100);
		} else {
			banzhenglv= df.format((float) 1 / 1 * 100);
		}
		return "{\"num_all\":\""+num_all+"\",\"done_num\":\""+done_num+"\",\"banzhenglv\":\""+banzhenglv+"\"}";
	}
	
	@Override
	public Object insert(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object update(Object jsonArray) throws Exception {
		return null;
	}
	
	@Override
	public Object delete(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object recordCount(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object setRequest(HttpServletRequest request) throws Exception {
		this.request = request;
		return request;
	}

	@Override
	public Object updateBatch(Object jsonArray) throws Exception {
		return null;
	}

	@Override
	public Object onCreate(Object jsonArray) {
		return null;
	}

	@Override
	public Object onDestory(Object jsonArray) {
		return null;
	}

	@Override
	public Object onInit(Object jsonArray) {
		return null;
	}
	
	//节育史
	 public Object query1(Object jsonArray) throws Exception {
	    	JSONArray array = JSONArray.fromObject(jsonArray);
			JSONObject jsonObject = JSONObject.fromObject(array.getString(0));
			String tbe_id = jsonObject.getString("tbe_id");
			
			String[] field_query1 = {"tbcsr_type","tbcsr_hospital","tbcsr_place","tbcsr_doctor","tbcsr_time","tbcsr_description"};
			String sql_query1="select tbcsr_type,tbcsr_hospital,tbcsr_place,tbcsr_doctor,tbcsr_time,tbcsr_description from tbc_surgery_record where tbe_id='"+tbe_id+"' ";
			String countSql="select count(*)  from tbc_surgery_record where tbe_id='"+tbe_id+"' ";
			return AccessDataBaseUtil.query4SQLPagging(field_query1, countSql, sql_query1, this.getPage(), this.getPagesize(), null);	
	 }
	
}


